package yeswecandb;

import com.mysql.jdbc.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author smiththa
 * Test
 */
public class YesWeCanDB {

    //!!To use the JDBC driver you must have the mySQL .jar file added to your libraries
    //Download it from here (ctrl+click): http://dev.mysql.com/downloads/connector/j/
    private final Connection connection;
    private final String url = "jdbc:mysql://localhost:3306/charter?zeroDateTimeBehavior=convertToNull";
    private final String username = "root", password = "nbuser";
    private String newDue_Date;

    public YesWeCanDB() throws ClassNotFoundException, InstantiationException, IllegalAccessException, SQLException {
        Class.forName("com.mysql.jdbc.Driver").newInstance();  //Makes an instance of a class that corresponds to the library class in quotations
        connection = (Connection) DriverManager.getConnection(url, username, password);  //Makes a connection using the driver instanciated above
    }

    public boolean login(String username, String password) throws SQLException {
        String SQLcommand = "SELECT * FROM Login\n"
                + "WHERE Login_ID='" + username + "' AND Password='" + password + "';";

        Statement statement = connection.createStatement();
        ResultSet result = statement.executeQuery(SQLcommand);
        return result.next();
    }

    private void addLogin(String loginID) throws SQLException {
        String password = "default";  //Could generate any password at random or based on a user's information instead
        String SQLcommand = "INSERT INTO Login (Login_ID,Password)\n"
                + "VALUES ('" + loginID + "','" + password + "');";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);

    }

    private void removeLogin(String loginID) throws SQLException {
        String SQLcommand = "DELETE FROM Login\n"
                + "WHERE Login_ID=" + loginID + ";";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

    public void changePassword(String loginID, String newPassword) throws SQLException {
        String SQLcommand = "UPDATE Login\n"
                + "SET Password='" + newPassword + "'"
                + "WHERE Login_ID=" + loginID + ";";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

// By Francis Tan    
    public boolean Calendar(String Assignment, String Due_Date) throws SQLException {
        String SQLcommand = "SELECT * FROM Calendar\n"
                + "WHERE Assignment='" + Assignment + "' AND Due_Date='" + Due_Date + "';";

        Statement statement = connection.createStatement();
        ResultSet result = statement.executeQuery(SQLcommand);
        return result.next();
    }

    private void addEvent(String Calendar) throws SQLException {
        String Due_Date = "default";  //Could generate any password at random or based on a user's information instead
        String SQLcommand = "INSERT INTO Calendar (Assignment,Due_Date)\n"
                + "VALUES ('" + Calendar + "','" + Due_Date + "');";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

    private void removeEvent(String Calendar) throws SQLException {
        String SQLcommand = "DELETE FROM Assignment\n"
                + "WHERE Assignment=" + Calendar + ";";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);

    }

    public void changeDue_Date(String Assignment, String DueDate) throws SQLException {
        String SQLcommand = "Update Assignment\n"
                + "SET Due_Date=' " + newDue_Date + " ' "
                + "WHERE Assignment=" + Assignment + ";";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

    private void addFaculty(String firstName, String lastName) throws SQLException {
        String SQLcommand = "INSERT INTO Faculty (First_Name, Last_Name)\n"
                + "VALUES ('" + firstName + "', '" + lastName + "');";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

    private void removeFaculty(int facultyID) throws SQLException {
        String SQLcommand = "DELETE FROM Faculty\n"
                + "WHERE ID=" + facultyID + ";";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

    private void addClass(String courseID, String time) throws SQLException {
        String SQLcommand = "INSERT INTO Class (Course_ID, Time)\n" + "VALUES ('" + courseID + "','" + time + "');";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

    private void removeClass(String classID) throws SQLException {
        String SQLcommand = "DELETE FROM Class\n " + "WHERE ID =" + classID + ";";
        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);

    }

    private void addCourse(String courseID, String subject) throws SQLException {
        String SQLcommand = "INSERT INTO Course (Course_ID, Subject\n"
                + "VALUES ('" + courseID + "', '" + subject + "');";
        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

    private void removeCourse(String courseID, String subject) throws SQLException {
        String SQLcommand = "DELETE FROM Course\n " + "WHERE ID =" + courseID + "', '" + subject + "'";
        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }

    public void addAssignment(int courseId, String title, String description, Date dateDue){
        try{
            String SQLcommand = "INSERT INTO ASSIGNMENT (COURSE_ID, TITLE, DESCRIPTION, DATE_DUE)"
                    +"VALUES ("+courseId + ", '" + title + "', '"
                    +description + "', '" + dateDue.toString() + "' );";
            
            Statement statement = connection.createStatement();
            statement.executeUpdate(SQLcommand);
        }catch(SQLException ex) {
            Logger.getLogger(YesWeCanDB.class.getName()).log(Level.SEVERE, null, ex);
        
        }
    }
    private void registerStudent(String studentID, String classID) throws SQLException{
        String SQLcommand = "INSERT INTO Registration (studentID, classID)\n" + "VALUES ('"+studentID+"','"+classID+"');";

        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
    }
    
    private void removeAssignment(int courseId) {
        try {
            String SQLcommand = "DELETE FROM ASSIGNMENT WHERE ID = " + courseId;
            
            Statement statement = connection.createStatement();
            statement.executeUpdate(SQLcommand);
        } catch (SQLException ex) {
            Logger.getLogger(YesWeCanDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    //By Ivan Ortiz
    //Assigns a faculty member to a class
    private void assignClass(String facultyID, String classID) throws SQLException
    {
        String SQLcommand = "INSERT INTO Assigned (faculty_id, class_id)\n" + "VALUES ('"+facultyID + "','"+classID+"');";
        
        Statement statement = connection.createStatement();
        statement.executeUpdate(SQLcommand);
        
    }
}
